package com.x.erp.dao.statis;

import com.x.erp.model.Car;
import com.x.erp.model.statis.MarketStorageVO;
import com.x.framework.dao.BaseDao;
import org.apache.commons.lang.StringUtils;
import org.springframework.stereotype.Repository;

import java.util.ArrayList;
import java.util.List;

@Repository
public class TVStatisDao extends BaseDao{

	private final static String searchMarketStorageInfoSql = "SELECT " +
			"em.*, " +
			"COUNT(ec.CAR_ID) AS STORAGE_NUM " +
			"FROM " +
			"erp_car ec, " +
			"erp_market em, " +
			"erp_market emp " +
			"WHERE " +
			"ec.MARKET_ID = em.MARKET_ID " +
			"AND emp.MARKET_ID = ? AND em.MARKET_CODE LIKE CONCAT(emp.MARKET_CODE, '%') " +
			"AND ec.CAR_STOCK_STATE > 10 ";
	/**
	 * 查询出库车龄排行
	 * @param marketStorageVO
	 * @return
	 */
	public List<MarketStorageVO> searchMarketStorageInfo(MarketStorageVO marketStorageVO) throws Exception {
		StringBuilder sql = new StringBuilder(searchMarketStorageInfoSql);
		List<Object> params = new ArrayList<>();
		params.add(marketStorageVO.getMarketId());
		carStatisSearchConditionDate(sql, params, marketStorageVO);
		sql.append("GROUP BY ec.MARKET_ID ");
		sql.append("ORDER BY STORAGE_NUM DESC ");
		return this.queryForList(sql.toString(), MarketStorageVO.class, params);
	}

	private final static String searchCarOutStorageInfoSql = "SELECT " +
			"em.*, " +
			"COUNT(ecs.SALE_ID) AS STORAGE_NUM " +
			"FROM " +
			"ERP_CAR ec, " +
			"erp_market em, " +
			"erp_market emp, " +
			"ERP_CAR_SALE ecs " +
			"WHERE " +
			"ec.MARKET_ID = em.MARKET_ID " +
			"AND emp.MARKET_ID = ? AND em.MARKET_CODE LIKE CONCAT(emp.MARKET_CODE, '%') " +
			"AND ec.car_id = ecs.car_id ";
	/**
	 * 查询车辆出库信息
	 * @param marketStorageVO
	 * @return
	 */
	public List<MarketStorageVO> searchCarOutStorageInfo(MarketStorageVO marketStorageVO) throws Exception {
		StringBuilder sql = new StringBuilder(searchCarOutStorageInfoSql);
		List<Object> params = new ArrayList<>();
		params.add(marketStorageVO.getMarketId());
		carSaleStatisSearchConditionDate(sql, params, marketStorageVO);
		sql.append("GROUP BY ec.MARKET_ID ");
		sql.append("ORDER BY STORAGE_NUM DESC ");
		return this.queryForList(sql.toString(), MarketStorageVO.class, params);
	}

	private final static String searchDayCarStorageInfoSql = "SELECT " +
			"COUNT(ec.CAR_ID) AS STORAGE_NUM, " +
			"DATE_FORMAT(ec.CAR_CREATE_TIME, '%Y-%m-%d') AS STATIS_DAY " +
			"FROM " +
			"erp_car ec, " +
			"erp_market em, " +
			"erp_market emp " +
			"WHERE " +
			"ec.CAR_STOCK_STATE > 10 " +
			"AND ec.MARKET_ID = em.MARKET_ID " +
			"AND emp.MARKET_ID = ? AND em.MARKET_CODE LIKE CONCAT(emp.MARKET_CODE, '%') ";
	/**
	 * 查询每日入库信息
	 * @param marketStorageVO
	 * @return
	 */
	public List<MarketStorageVO> searchDayCarStorageInfo(MarketStorageVO marketStorageVO) throws Exception {
		StringBuilder sql = new StringBuilder(searchDayCarStorageInfoSql);
		List<Object> params = new ArrayList<>();
		params.add(marketStorageVO.getMarketId());
		carStatisSearchConditionDate(sql, params, marketStorageVO);
		sql.append("GROUP BY STATIS_DAY ");
		sql.append("ORDER BY STATIS_DAY ");
		return this.queryForList(sql.toString(), MarketStorageVO.class, params);
	}

	private final static String searchDayCarOutStorageInfoSql = "SELECT " +
			"COUNT(ecs.SALE_ID) AS STORAGE_NUM, " +
			"DATE_FORMAT(ecs.SALE_DEAL_DATE, '%Y-%m-%d') AS STATIS_DAY " +
			"FROM " +
			"erp_car ec, " +
			"ERP_CAR_SALE ecs, " +
			"erp_market em, " +
			"erp_market emp " +
			"WHERE " +
			"ec.car_id = ecs.car_id " +
			"AND ec.MARKET_ID = em.MARKET_ID " +
			"AND emp.MARKET_ID = ? AND em.MARKET_CODE LIKE CONCAT(emp.MARKET_CODE, '%') ";
	/**
	 * 查询每日出库信息
	 * @param marketStorageVO
	 * @return
	 */
	public List<MarketStorageVO> searchDayCarOutStorageInfo(MarketStorageVO marketStorageVO) throws Exception {
		StringBuilder sql = new StringBuilder(searchDayCarOutStorageInfoSql);
		List<Object> params = new ArrayList<>();
		params.add(marketStorageVO.getMarketId());
		carSaleStatisSearchConditionDate(sql, params, marketStorageVO);
		sql.append("GROUP BY STATIS_DAY ");
		sql.append("ORDER BY STATIS_DAY ");
		return this.queryForList(sql.toString(), MarketStorageVO.class, params);
	}

	private final static String searchDayCarSaleVolumeSql = "SELECT " +
			"SUM(ecs.SALE_PRICE) AS SALE_VOLUME, " +
			"DATE_FORMAT(ecs.SALE_DEAL_DATE, '%Y-%m-%d') AS STATIS_DAY " +
			"FROM " +
			"erp_car ec, " +
			"ERP_CAR_SALE ecs, " +
			"erp_market em, " +
			"erp_market emp " +
			"WHERE " +
			"ec.car_id = ecs.car_id " +
			"AND ec.MARKET_ID = em.MARKET_ID " +
			"AND emp.MARKET_ID = ? AND em.MARKET_CODE LIKE CONCAT(emp.MARKET_CODE, '%') ";
	/**
	 * 查询每日销售信息
	 * @param marketStorageVO
	 * @return
	 */
	public List<MarketStorageVO> searchDaySaleVolume(MarketStorageVO marketStorageVO) throws Exception {
		StringBuilder sql = new StringBuilder(searchDayCarSaleVolumeSql);
		List<Object> params = new ArrayList<>();
		params.add(marketStorageVO.getMarketId());
		carSaleStatisSearchConditionDate(sql, params, marketStorageVO);
		sql.append("GROUP BY STATIS_DAY ");
		sql.append("ORDER BY STATIS_DAY ");
		return this.queryForList(sql.toString(), MarketStorageVO.class, params);
	}

	private final static String searchCarListSql = "SELECT " +
			"ec.CAR_ID, " +
			"ec.CAR_VIN, " +
			"ec.CAR_RETAIL_PRICE, " +
			"ecl.CAR_LOG_CREATE_TIME AS CAR_CREATE_TIME, " +
			"bs.SERIES_NAME, " +
			"bb.BRAND_NAME, " +
			"em.MARKET_NAME, " +
			"em.MARKET_SHORT_NAME " +
			"FROM " +
			"erp_car ec, " +
			"erp_car_log ecl, " +
			"x_base.base_series bs, " +
			"x_base.base_brand bb, " +
			"erp_market em, " +
			"erp_market emp " +
			"WHERE " +
			"ec.car_id = ecl.car_id " +
			"AND CAR_STATE_KEY = 'carStockState' " +
			"AND CAR_STATE_VALUE = 20 " +
			"AND ec.SERIES_ID = bs.SERIES_ID " +
			"AND bs.BRAND_ID = bb.BRAND_ID " +
			"AND ec.MARKET_ID = em.MARKET_ID " +
			"AND emp.MARKET_ID = ? AND em.MARKET_CODE LIKE CONCAT(emp.MARKET_CODE, '%') ";
	/**
	 * 查询入库车辆列表
	 * @param marketStorageVO
	 * @return
	 */
	public List<Car> searchCarList(MarketStorageVO marketStorageVO) throws Exception {
		StringBuilder sql = new StringBuilder(searchCarListSql);
		List<Object> params = new ArrayList<>();
		params.add(marketStorageVO.getMarketId());
		carStatisSearchCondition(sql, params, marketStorageVO);
		if(marketStorageVO.getEndDate() != null){
			sql.append("AND ecl.CAR_LOG_CREATE_TIME > ? ");
			params.add(marketStorageVO.getEndDate());
		}
		sql.append("GROUP BY ec.CAR_ID ");
		sql = new StringBuilder("SELECT * FROM ( ").append(sql).append(") T ");
		sql.append("ORDER BY T.CAR_CREATE_TIME DESC ");
		getLimitSql(sql, marketStorageVO.getPageSize());
		return this.queryForList(sql.toString(), Car.class, params);
	}

	private final static String searchCarSaleListSql = "SELECT " +
			"ec.CAR_ID, " +
			"ec.CAR_VIN, " +
			"ec.CAR_RETAIL_PRICE, " +
			"ec.CAR_CREATE_TIME, " +
			"bs.SERIES_NAME, " +
			"bb.BRAND_NAME, " +
			"em.MARKET_NAME, " +
			"em.MARKET_SHORT_NAME " +
			"FROM " +
			"erp_car ec, " +
			"erp_car_sale ecs, " +
			"x_base.base_series bs, " +
			"x_base.base_brand bb, " +
			"erp_market em, " +
			"erp_market emp " +
			"WHERE " +
			"ec.CAR_ID = ecs.CAR_ID " +
			"AND ec.SERIES_ID = bs.SERIES_ID " +
			"AND bs.BRAND_ID = bb.BRAND_ID " +
			"AND ec.MARKET_ID = em.MARKET_ID " +
			"AND emp.MARKET_ID = ? AND em.MARKET_CODE LIKE CONCAT(emp.MARKET_CODE, '%') ";
	/**
	 * 查询出库车辆列表
	 * @param marketStorageVO
	 * @return
	 */
	public List<Car> searchCarSaleList(MarketStorageVO marketStorageVO) throws Exception {
		StringBuilder sql = new StringBuilder(searchCarSaleListSql);
		List<Object> params = new ArrayList<>();
		params.add(marketStorageVO.getMarketId());
		carSaleStatisSearchConditionDate(sql, params, marketStorageVO);
		if(marketStorageVO.getEndDate() != null){
			sql.append("AND ecs.SALE_DEAL_DATE > ? ");
			params.add(marketStorageVO.getEndDate());
		}
		sql.append("ORDER BY ec.CAR_CREATE_TIME DESC ");
		getLimitSql(sql, marketStorageVO.getPageSize());
		return this.queryForList(sql.toString(), Car.class, params);
	}

	private final static String searchStorageColorRankSql = "SELECT " +
			"COUNT(ec.CAR_ID) AS STORAGE_NUM, " +
			"ec.CAR_OUT_COLOR " +
			"FROM " +
			"erp_car ec, " +
			"erp_market em, " +
			"erp_market emp " +
			"WHERE " +
			"ec.CAR_STOCK_STATE > 10 " +
			"AND ec.CAR_STOCK_STATE < 40 " +
			"AND ec.CAR_OUT_COLOR != '' " +
			"AND ec.MARKET_ID = em.MARKET_ID " +
			"AND emp.MARKET_ID = ? AND em.MARKET_CODE LIKE CONCAT(emp.MARKET_CODE, '%') ";
	/**
	 * 查询库存车辆颜色排行
	 * @param marketStorageVO
	 * @return
	 */
	public List<MarketStorageVO> searchStorageColorRank(MarketStorageVO marketStorageVO) throws Exception {
		StringBuilder sql = new StringBuilder(searchStorageColorRankSql);
		List<Object> params = new ArrayList<>();
		params.add(marketStorageVO.getMarketId());
		carStatisSearchConditionDate(sql, params, marketStorageVO);
		sql.append("GROUP BY ec.CAR_OUT_COLOR ");
		sql.append("ORDER BY STORAGE_NUM DESC ");
		getLimitSql(sql, marketStorageVO.getPageSize());
		return this.queryForList(sql.toString(), MarketStorageVO.class, params);
	}

	private final static String searchCarOutStockColorRankSql = "SELECT " +
			"COUNT(ec.CAR_ID) AS STORAGE_NUM, " +
			"ec.CAR_OUT_COLOR " +
			"FROM " +
			"erp_car ec, " +
			"erp_car_sale ecs, " +
			"erp_market em, " +
			"erp_market emp " +
			"WHERE " +
			"ec.CAR_ID = ecs.CAR_ID " +
			"AND ec.CAR_OUT_COLOR != '' " +
			"AND ec.MARKET_ID = em.MARKET_ID " +
			"AND emp.MARKET_ID = ? AND em.MARKET_CODE LIKE CONCAT(emp.MARKET_CODE, '%') ";
	/**
	 * 查询出库车辆颜色排行
	 * @param marketStorageVO
	 * @return
	 */
	public List<MarketStorageVO> searchCarOutStockColorRank(MarketStorageVO marketStorageVO) throws Exception {
		StringBuilder sql = new StringBuilder(searchCarOutStockColorRankSql);
		List<Object> params = new ArrayList<>();
		params.add(marketStorageVO.getMarketId());
		carSaleStatisSearchConditionDate(sql, params, marketStorageVO);
		sql.append("GROUP BY ec.CAR_OUT_COLOR ");
		sql.append("ORDER BY STORAGE_NUM DESC ");
		getLimitSql(sql, marketStorageVO.getPageSize());
		return this.queryForList(sql.toString(), MarketStorageVO.class, params);
	}

	private final static String searchCarOutStockPriceRankSql = "SELECT " +
			"COUNT(ecs.SALE_ID) AS STORAGE_NUM, " +
			"bp.PRICE_REMARK " +
			"FROM " +
			"erp_car ec, " +
			"erp_car_sale ecs, " +
			"base_price bp, " +
			"erp_market em, " +
			"erp_market emp " +
			"WHERE " +
			"ec.CAR_ID = ecs.CAR_ID " +
			"AND ecs.SALE_PRICE >= bp.PRICE_FROM_10000 " +
			"AND ecs.SALE_PRICE < bp.PRICE_TO_10000 " +
			"AND ec.MARKET_ID = em.MARKET_ID " +
			"AND emp.MARKET_ID = ? AND em.MARKET_CODE LIKE CONCAT(emp.MARKET_CODE, '%') ";
	/**
	 * 价格排行
	 * @param marketStorageVO
	 * @return
	 */
	public List<MarketStorageVO> searchCarOutStockPriceRank(MarketStorageVO marketStorageVO) throws Exception {
		StringBuilder sql = new StringBuilder(searchCarOutStockPriceRankSql);
		List<Object> params = new ArrayList<>();
		params.add(marketStorageVO.getMarketId());
		carSaleStatisSearchConditionDate(sql, params, marketStorageVO);
		sql.append("GROUP BY bp.PRICE_REMARK ");
		sql.append("ORDER BY bp.PRICE_FROM_10000 ");
		getLimitSql(sql, marketStorageVO.getPageSize());
		return this.queryForList(sql.toString(), MarketStorageVO.class, params);
	}

	private final static String searchCarOutStockRankSql = "SELECT " +
			"COUNT(ecs.SALE_ID) AS STORAGE_NUM, " +
			"es.SHOP_NAME, " +
			"es.SHOP_SHORT_NAME " +
			"FROM " +
			"erp_car ec, " +
			"erp_car_sale ecs, " +
			"erp_market em, " +
			"erp_market emp, " +
			"erp_shop es " +
			"WHERE " +
			"ec.CAR_ID = ecs.CAR_ID " +
			"AND ec.SHOP_ID = es.SHOP_ID " +
			"AND ec.MARKET_ID = em.MARKET_ID " +
			"AND emp.MARKET_ID = ? AND em.MARKET_CODE LIKE CONCAT(emp.MARKET_CODE, '%') ";
	/**
	 * 出库量排行
	 * @param marketStorageVO
	 * @return
	 */
	public List<MarketStorageVO> searchCarOutStockRank(MarketStorageVO marketStorageVO) throws Exception {
		StringBuilder sql = new StringBuilder(searchCarOutStockRankSql);
		List<Object> params = new ArrayList<>();
		params.add(marketStorageVO.getMarketId());
		carSaleStatisSearchConditionDate(sql, params, marketStorageVO);
		sql.append("GROUP BY es.SHOP_NAME ");
		sql.append("ORDER BY STORAGE_NUM DESC ");
		getLimitSql(sql, marketStorageVO.getPageSize());
		return this.queryForList(sql.toString(), MarketStorageVO.class, params);
	}

	private final static String searchCarSaleVolumeRankSql = "SELECT " +
			"SUM(ecs.SALE_PRICE) AS SALE_VOLUME, " +
			"es.SHOP_NAME, " +
			"es.SHOP_SHORT_NAME " +
			"FROM " +
			"erp_car ec, " +
			"erp_car_sale ecs, " +
			"erp_market em, " +
			"erp_market emp, " +
			"erp_shop es " +
			"WHERE " +
			"ec.CAR_ID = ecs.CAR_ID " +
			"AND ec.SHOP_ID = es.SHOP_ID " +
			"AND ec.MARKET_ID = em.MARKET_ID " +
			"AND emp.MARKET_ID = ? AND em.MARKET_CODE LIKE CONCAT(emp.MARKET_CODE, '%') ";
	/**
	 * 销售额排行
	 * @param marketStorageVO
	 * @return
	 */
	public List<MarketStorageVO> searchCarSaleVolumeRank(MarketStorageVO marketStorageVO) throws Exception {
		StringBuilder sql = new StringBuilder(searchCarSaleVolumeRankSql);
		List<Object> params = new ArrayList<>();
		params.add(marketStorageVO.getMarketId());
		carSaleStatisSearchConditionDate(sql, params, marketStorageVO);
		sql.append("GROUP BY es.SHOP_NAME ");
		sql.append("ORDER BY SALE_VOLUME DESC ");
		getLimitSql(sql, marketStorageVO.getPageSize());
		return this.queryForList(sql.toString(), MarketStorageVO.class, params);
	}

	private final static String searchCarStockSql = "SELECT " +
			"COUNT(ec.CAR_ID) AS STORAGE_NUM " +
			"FROM " +
			"erp_car ec, " +
			"erp_market em, " +
			"erp_market emp " +
			"WHERE " +
			"ec.CAR_STOCK_STATE > 10 " +
			"AND ec.CAR_STOCK_STATE < 40 " +
			"AND ec.MARKET_ID = em.MARKET_ID " +
			"AND emp.MARKET_ID = ? AND em.MARKET_CODE LIKE CONCAT(emp.MARKET_CODE, '%') ";
	/**
	 * 库存量
	 * @param marketStorageVO
	 * @return
	 */
	public int searchCarStock(MarketStorageVO marketStorageVO) {
		StringBuilder sql = new StringBuilder(searchCarStockSql);
		List<Object> params = new ArrayList<>();
		params.add(marketStorageVO.getMarketId());
		carStatisSearchConditionDate(sql, params, marketStorageVO);
		return this.queryForInt(sql.toString(), params);
	}

	private final static String searchCarDisplacementRankSql = "SELECT " +
			"COUNT(ecs.SALE_ID) AS STORAGE_NUM, " +
			"bd.CAR_DISPLACEMENT_REMARK " +
			"FROM " +
			"erp_car ec, " +
			"erp_car_sale ecs, " +
			"erp_market em, " +
			"erp_market emp, " +
			"( " +
			"SELECT 0 AS DISPLACEMENT_FROM,1.6 AS DISPLACEMENT_TO, '1.6L以下' AS CAR_DISPLACEMENT_REMARK " +
			"UNION " +
			"SELECT 1.6,2.0, '1.6L~2.0L' " +
			"UNION " +
			"SELECT 2.0,2.5, '2.0L~2.5L' " +
			"UNION " +
			"SELECT 2.5,10, '2.5L以上' " +
			") bd " +
			"WHERE " +
			"ec.CAR_ID = ecs.CAR_ID " +
			"AND ec.CAR_DISPLACEMENT >= bd.DISPLACEMENT_FROM " +
			"AND ec.CAR_DISPLACEMENT < bd.DISPLACEMENT_TO " +
			"AND ec.MARKET_ID = em.MARKET_ID " +
			"AND emp.MARKET_ID = ? AND em.MARKET_CODE LIKE CONCAT(emp.MARKET_CODE, '%') ";
	/**
	 * 查询排量排行
	 * @param marketStorageVO
	 * @return
	 */
	public List<MarketStorageVO> searchCarDisplacementRank(MarketStorageVO marketStorageVO) throws Exception {
		StringBuilder sql = new StringBuilder(searchCarDisplacementRankSql);
		List<Object> params = new ArrayList<>();
		params.add(marketStorageVO.getMarketId());
		carSaleStatisSearchConditionDate(sql, params, marketStorageVO);
		sql.append("GROUP BY bd.CAR_DISPLACEMENT_REMARK ");
		sql.append("ORDER BY STORAGE_NUM DESC ");
		getLimitSql(sql, marketStorageVO.getPageSize());
		return this.queryForList(sql.toString(), MarketStorageVO.class, params);
	}

	private final static String searchCarFirstDetectInfoSql = "SELECT " +
			"COUNT(DISTINCT ecl.CAR_ID) AS STORAGE_NUM " +
			"FROM " +
			"erp_car ec, " +
			"erp_car_log ecl, " +
			"erp_market em, " +
			"erp_market emp " +
			"WHERE " +
			"ec.CAR_ID = ecl.CAR_ID  " +
			"AND ec.MARKET_ID = em.MARKET_ID " +
			"AND emp.MARKET_ID = ? " +
			"AND em.MARKET_CODE LIKE CONCAT(emp.MARKET_CODE, '%') ";
	/**
	 * 查询初检数量
	 * @param marketStorageVO
	 * @return
	 */
	public int searchCarFirstDetectInfo(MarketStorageVO marketStorageVO) {
		StringBuilder sql = new StringBuilder(searchCarFirstDetectInfoSql);
		List<Object> params = new ArrayList<>();
		params.add(marketStorageVO.getMarketId());
		carStatisSearchCondition(sql, params, marketStorageVO);
		if(marketStorageVO.getCarDetectState() != null){
			sql.append("AND ecl.CAR_STATE_VALUE = ? ");
			params.add(marketStorageVO.getCarDetectState());
		}
		return this.queryForInt(sql.toString(), params);
	}

	private final static String searchDayCarFirstDetectInfoSql = "SELECT " +
			"COUNT(DISTINCT ecl.CAR_ID) AS STORAGE_NUM, " +
			"DATE_FORMAT(ecl.CAR_LOG_CREATE_TIME, '%Y-%m-%d') AS STATIS_DAY " +
			"FROM " +
			"erp_car ec, " +
			"erp_car_log ecl, " +
			"erp_market em, " +
			"erp_market emp " +
			"WHERE " +
			"ec.CAR_ID = ecl.CAR_ID " +
			"AND (ecl.CAR_STATE_VALUE = 510 OR ecl.CAR_STATE_VALUE = 520) " +
			"AND ec.MARKET_ID = em.MARKET_ID " +
			"AND emp.MARKET_ID = ? AND em.MARKET_CODE LIKE CONCAT(emp.MARKET_CODE, '%') ";
	/**
	 * 查询每日初检信息
	 * @param marketStorageVO
	 * @return
	 */
	public List<MarketStorageVO> searchDayCarFirstDetectInfo(MarketStorageVO marketStorageVO) throws Exception {
		StringBuilder sql = new StringBuilder(searchDayCarFirstDetectInfoSql);
		List<Object> params = new ArrayList<>();
		params.add(marketStorageVO.getMarketId());
		carStatisSearchCondition(sql, params, marketStorageVO);
		if(marketStorageVO.getStartDate() != null){
			sql.append("AND ecl.CAR_LOG_CREATE_TIME >= ? ");
			params.add(marketStorageVO.getStartDate());
		}
		if(marketStorageVO.getEndDate() != null){
			sql.append("AND ecl.CAR_LOG_CREATE_TIME < ? ");
			params.add(marketStorageVO.getEndDate());
		}
		sql.append("GROUP BY STATIS_DAY ");
		sql.append("ORDER BY STATIS_DAY ");
		return this.queryForList(sql.toString(), MarketStorageVO.class, params);
	}

	private final static String searchCarAuthNumSql = "SELECT " +
			"COUNT(ec.CAR_ID) AS STORAGE_NUM " +
			"FROM " +
			"erp_car ec, " +
			"erp_car_sale ecs, " +
			"erp_market em, " +
			"erp_market emp " +
			"WHERE " +
			"ec.CAR_ID = ecs.CAR_ID " +
			"AND ec.MARKET_ID = em.MARKET_ID " +
			"AND emp.MARKET_ID = ? AND em.MARKET_CODE LIKE CONCAT(emp.MARKET_CODE, '%') ";
	/**
	 * 查询认证数量
	 * @param marketStorageVO
	 * @return
	 */
	public int searchCarAuthNum(MarketStorageVO marketStorageVO) {
		StringBuilder sql = new StringBuilder(searchCarAuthNumSql);
		List<Object> params = new ArrayList<>();
		params.add(marketStorageVO.getMarketId());
		carStatisSearchConditionDate(sql, params, marketStorageVO);
		return this.queryForInt(sql.toString(), params);
	}

	private final static String searchStockBrandRankSql = "SELECT " +
			"COUNT(ec.CAR_ID) as STORAGE_NUM, " +
			"bb.BRAND_NAME " +
			"FROM " +
			"ERP_CAR ec, " +
			"erp_market em, " +
			"erp_market emp, " +
			"x_base.BASE_BRAND bb " +
			"WHERE " +
			"ec.CAR_STOCK_STATE > 10 " +
			"AND ec.CAR_STOCK_STATE < 40 " +
			"AND ec.BRAND_ID = bb.BRAND_ID " +
			"AND ec.MARKET_ID = em.MARKET_ID " +
			"AND emp.MARKET_ID = ? AND em.MARKET_CODE LIKE CONCAT(emp.MARKET_CODE, '%') ";
	/**
	 * 查询库存品牌排行
	 * @param marketStorageVO
	 * @return
	 */
	public List<MarketStorageVO> searchStockBrandRank(MarketStorageVO marketStorageVO) throws Exception {
		StringBuilder sql = new StringBuilder(searchStockBrandRankSql);
		List<Object> params = new ArrayList<>();
		params.add(marketStorageVO.getMarketId());
		carStatisSearchConditionDate(sql, params, marketStorageVO);
		sql.append("GROUP BY ec.BRAND_ID ");
		sql.append("ORDER BY STORAGE_NUM DESC ");
		getLimitSql(sql, marketStorageVO.getPageSize());
		return this.queryForList(sql.toString(), MarketStorageVO.class, params);
	}

	private final static String searchCarOutAndInBrandContrastSql = "SELECT " +
			"bb.BRAND_NAME, " +
			"COUNT(ec.CAR_ID) AS STORAGE_NUM, " +
			"(SELECT COUNT(DISTINCT ecs.CAR_ID) FROM erp_car t1,erp_car_sale ecs WHERE t1.CAR_ID = ecs.CAR_ID AND t1.BRAND_ID = bb.BRAND_ID) AS CAR_OUT_STORAGE_NUM " +
			"FROM " +
			"erp_car ec, " +
			"erp_market em, " +
			"erp_market emp, " +
			"x_base.base_brand bb " +
			"WHERE " +
			"ec.BRAND_ID = bb.BRAND_ID " +
			"AND ec.MARKET_ID = em.MARKET_ID " +
			"AND emp.MARKET_ID = ? AND em.MARKET_CODE LIKE CONCAT(emp.MARKET_CODE, '%') ";
	/**
	 * 查询出/入库品牌对比
	 * @param marketStorageVO
	 * @return
	 */
	public List<MarketStorageVO> searchCarOutAndInBrandContrast(MarketStorageVO marketStorageVO) throws Exception {
		StringBuilder sql = new StringBuilder(searchCarOutAndInBrandContrastSql);
		List<Object> params = new ArrayList<>();
		params.add(marketStorageVO.getMarketId());
		carStatisSearchConditionDate(sql, params, marketStorageVO);
		sql.append("GROUP BY bb.BRAND_ID ");
		sql.append("ORDER BY CAR_OUT_STORAGE_NUM DESC ");
		getLimitSql(sql, marketStorageVO.getPageSize());
		return this.queryForList(sql.toString(), MarketStorageVO.class, params);
	}

	private final static String searchCarSaleVolumeSql = "SELECT " +
			"SUM(ecs.SALE_PRICE) AS SALE_VOLUME " +
			"FROM " +
			"erp_car ec, " +
			"ERP_CAR_SALE ecs, " +
			"erp_market em, " +
			"erp_market emp " +
			"WHERE " +
			"ec.car_id = ecs.car_id " +
			"AND ec.MARKET_ID = em.MARKET_ID " +
			"AND emp.MARKET_ID = ? AND em.MARKET_CODE LIKE CONCAT(emp.MARKET_CODE, '%') ";
	/**
	 * 查询交易额
	 * @param marketStorageVO
	 * @return
	 */
	public MarketStorageVO searchCarSaleVolume(MarketStorageVO marketStorageVO) throws Exception {
		StringBuilder sql = new StringBuilder(searchCarSaleVolumeSql);
		List<Object> params = new ArrayList<>();
		params.add(marketStorageVO.getMarketId());
		carSaleStatisSearchConditionDate(sql, params, marketStorageVO);
		return this.queryForList(sql.toString(), MarketStorageVO.class, params).get(0);
	}

	private void carStatisSearchCondition(StringBuilder sql, List<Object> params, MarketStorageVO marketStorageVO){
		if(marketStorageVO != null) {
			if (StringUtils.isNotEmpty(marketStorageVO.getShopId())) {
				sql.append("AND ec.SHOP_ID = ? ");
				params.add(marketStorageVO.getShopId());
			}
			if (marketStorageVO.getCarAuthState() != null){
				sql.append("AND ec.CAR_AUTH_STATE = ? ");
				params.add(marketStorageVO.getCarAuthState());
			}
			if (marketStorageVO.getCarSaleState() != null){
				sql.append("AND ec.CAR_SALE_STATE = ? ");
				params.add(marketStorageVO.getCarSaleState());
			}
			if (marketStorageVO.getBrandId() != null){
				sql.append("AND ec.BRAND_ID = ? ");
				params.add(marketStorageVO.getBrandId());
			}
			if (marketStorageVO.getSeriesId() != null){
				sql.append("AND ec.SERIES_ID = ? ");
				params.add(marketStorageVO.getSeriesId());
			}
			if (StringUtils.isNotEmpty(marketStorageVO.getCarOutColor())){
				sql.append("AND ec.CAR_OUT_COLOR = ? ");
				params.add(marketStorageVO.getCarOutColor());
			}
		}
	}

	private void carStatisSearchConditionDate(StringBuilder sql, List<Object> params, MarketStorageVO marketStorageVO){
		if(marketStorageVO != null) {
			carStatisSearchCondition(sql, params, marketStorageVO);
			if(marketStorageVO.getStartDate() != null){
				sql.append("AND ec.CAR_CREATE_TIME >= ? ");
				params.add(marketStorageVO.getStartDate());
			}
			if(marketStorageVO.getEndDate() != null){
				sql.append("AND ec.CAR_CREATE_TIME < ? ");
				params.add(marketStorageVO.getEndDate());
			}
		}
	}

	private void carSaleStatisSearchConditionDate(StringBuilder sql, List<Object> params, MarketStorageVO marketStorageVO){
		if(marketStorageVO != null) {
			carStatisSearchCondition(sql, params, marketStorageVO);
			if(marketStorageVO.getStartDate() != null){
				sql.append("AND ecs.SALE_DEAL_DATE >= ? ");
				params.add(marketStorageVO.getStartDate());
			}
			if(marketStorageVO.getEndDate() != null){
				sql.append("AND ecs.SALE_DEAL_DATE < ? ");
				params.add(marketStorageVO.getEndDate());
			}
		}
	}

	/**
	 * 分页Sql
	 * @param sql
	 * @param pageSize
	 */
	private void getLimitSql(StringBuilder sql, Integer pageSize) {
		if(pageSize != null && pageSize > 0) {
			sql.append("LIMIT ").append(pageSize);
		} else {
			sql.append("LIMIT 5");
		}
	}

}
